package com.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.utils.DbUtils;
import com.utils.SqlUtils;
import com.utils.SqlUtils.ColInfo;

/**
 * 基本CRUD都可以写在这边，然后dao继承使用，都封装起来了
 * 
 * 增删改都必须调用提交函数，查询不用！！！
 * 
 * @author Administrator
 * 
 * @param <T>
 */
public class BaseDao<T extends BasePojo> {

	/**
	 * 所有表都适用的插入方法
	 * 
	 * @param tuser
	 *            待插入的对象
	 * @return 返回该对象信息
	 * @throws SQLException
	 */
	public T create(T tuser) throws SQLException  {
		Connection conn = DbUtils.getConn();
		// 1获取表名
		String tableName = SqlUtils.getTableName(tuser.getClass());
		// 2设置自增字段
		Long id = DbUtils.getSeq(tableName + "_seq");
		tuser.setId(id);// 因为这个类都是基础的类，不允许有不是基础存在，所以使用继承与泛型

		ColInfo cinfo = SqlUtils.getColArr(tuser.getClass());
		String colstr = SqlUtils.list2colstr(cinfo.cols);
		String colstrq = SqlUtils.list2colqqq(cinfo.cols.size());
		// 表+字段+？---》insert into t_user( id,user_name,sex,birth,email,user_password,operate_time) values (?,?,?,?,?,?,? )
		String sql = "insert into " + tableName + "( " + colstr + ") values ( "
				+ colstrq + " )";
		System.out.println(sql);
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			SqlUtils.setParaByPerstmt(tuser, cinfo, ps);
			int ret = ps.executeUpdate();
//			ConnectionFactory.commit();
			if (ret != 1) {
				throw new SQLException("插入数据失败!");
			} 
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return getById(id, tuser);
	}

	
	
	/**
	 * 对象的更新
	 * @param tuser
	 * @return
	 * @throws SQLException
	 */
	public T updateById(T tuser) throws SQLException {
		Connection conn = DbUtils.getConn();
		// 1获取表名
		String tableName = SqlUtils.getTableName(tuser.getClass());
		Long id = (Long) tuser.getId();

		ColInfo cinfo = SqlUtils.getColArr(tuser.getClass());
		String colstrSet = SqlUtils.list2Set(cinfo.cols, ",");
		String colstrq = SqlUtils.list2colqqq(cinfo.cols.size());
	
		String sql = "update " + tableName +" set "+  colstrSet + " where id="+id ;
		System.out.println(sql);
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			SqlUtils.setParaByPerstmt2(tuser, cinfo, ps);
			int ret = ps.executeUpdate();
			if (ret != 1) {
				throw new SQLException("插入数据失败!");
			} 
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return getById(id, tuser);
	}
	


	/**
	 * 对象单个属性更新
	 * @param tuser
	 * @return
	 * @throws SQLException
	 */
	public T updateByKey(String col,String value,Long id,T tuser) throws SQLException {
		Connection conn = DbUtils.getConn();
		// 1获取表名
		String tableName = SqlUtils.getTableName(tuser.getClass());
//		Long id = (Long) tuser.getId();

		ColInfo cinfo = SqlUtils.getColArr(tuser.getClass());
		String colstrSet = SqlUtils.list2Set(cinfo.cols, ",");
		String colstrq = SqlUtils.list2colqqq(cinfo.cols.size());
	
		String sql = "update " + tableName +" set "+  col+"='"+value + "' where id="+id ;
		System.out.println(sql);
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
	//		SqlUtils.setParaByPerstmt2(tuser, cinfo, ps);
			int ret = ps.executeUpdate();
			if (ret != 1) {
				throw new SQLException("更新数据失败!");
			} 
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			try {
				
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return getById(id, tuser);
	}
	
	
	/**
	 * 通过id查找用户，还要传入对象，把该对象对应的表，方法，字段等获取出来
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public T getById(Long id, T tuser)  {

		Connection conn = DbUtils.getConn();
		String tableName = SqlUtils.getTableName(tuser.getClass());
		Object o = null;

		PreparedStatement ps = null;
		try {
			o = Class.forName(tuser.getClass().getName()).newInstance();// 对象的实例化
			ColInfo cinfo = SqlUtils.getColArr(o.getClass());
			String colstr = SqlUtils.list2colstr(cinfo.cols2);
			String sql = "select " + colstr + " from " + tableName
					+ " where id=?";
			System.out.println(sql);

			ps = conn.prepareStatement(sql);
			ps.setLong(1, id);
			ResultSet rs = ps.executeQuery();
	//		ConnectionFactory.commit();

			while (rs.next()) {
				// 对传进去的对象o进行赋值
				SqlUtils.setParaByRs(o, cinfo, rs);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return (T) o;
	}
	
	


	/**
	 * 根据id删除对象
	 * @param id
	 * @param t
	 * @throws SQLException 
	 */
	public int deleteById(Long id, Class<T> className) throws SQLException {
		Connection conn = DbUtils.getConn();
		String tableName = SqlUtils.getTableName(className);

		int ret=0;
		PreparedStatement ps = null;
		try {

			String sql = "delete from " + tableName + " where id=?";
			System.out.println(sql);
			ps = conn.prepareStatement(sql);
			ps.setLong(1, id);
			ret=ps.executeUpdate();
		//	ConnectionFactory.commit();

		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		return  ret;
	}
	
	
	/**
	 * 根据key删除对象
	 * @param id
	 * @param t
	 * @throws SQLException 
	 */
	public int deleteByKey(String col,String key, T t) throws SQLException {
		Connection conn = DbUtils.getConn();
		String tableName = SqlUtils.getTableName(t.getClass());

		int ret=0;
		PreparedStatement ps = null;
		try {

			String sql = "delete from " + tableName + " where "+col+" =?";
			System.out.println(sql);
			ps = conn.prepareStatement(sql);
			ps.setString(1, key);
			ret=ps.executeUpdate();
		//	ConnectionFactory.commit();

		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		return  ret;
	}
	
	
	/**
	 * 获取表t的所有对象
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public List<T> getAll(T t)  {

		List objList=new ArrayList();
		Connection conn = DbUtils.getConn();
		String tableName = SqlUtils.getTableName(t.getClass());


		PreparedStatement ps = null;
		try {
			Object oTest = Class.forName(t.getClass().getName()).newInstance();// 对象的实例化
			ColInfo cinfo = SqlUtils.getColArr(oTest.getClass());
			String colstr = SqlUtils.list2colstr(cinfo.cols2);
			String sql = "select " + colstr + " from " + tableName;
			System.out.println(sql);

			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
	//		ConnectionFactory.commit();
			while (rs.next()) {
				Object o = Class.forName(t.getClass().getName()).newInstance();// 对象的实例化
				// 对传进去的对象o进行赋值
				SqlUtils.setParaByRs(o, cinfo, rs);
				//obj
				objList.add(o);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			//throw e;
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return objList;
	}

	/**
	 * 通过key查找用户，数据库列名自己输入
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public List<T> getByKey(String col,String key, T t)  {


		List objList=new ArrayList();
		Connection conn = DbUtils.getConn();
		String tableName = SqlUtils.getTableName(t.getClass());


		PreparedStatement ps = null;
		try {
			Object oTest = Class.forName(t.getClass().getName()).newInstance();// 对象的实例化
			ColInfo cinfo = SqlUtils.getColArr(oTest.getClass());
			String colstr = SqlUtils.list2colstr(cinfo.cols2);
//			String sql = "select " + colstr + " from " + tableName+" where "+col+" = '"+key+"'";
			String sql = "select " + colstr + " from " + tableName+" where "+col+" = ?";
			System.out.println(sql);

			ps = conn.prepareStatement(sql);
			ps.setString(1, key);
			ResultSet rs = ps.executeQuery();
	//		ConnectionFactory.commit();

			while (rs.next()) {
				Object o = Class.forName(t.getClass().getName()).newInstance();// 对象的实例化
				// 对传进去的对象o进行赋值
				SqlUtils.setParaByRs(o, cinfo, rs);
				//obj
				objList.add(o);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return objList;
	}
	
	
	/**
	 * 获取表t的所有对象
	 * @param id
	 * @param page 页数
	 * @return
	 * @throws SQLException
	 */
	public List<T> getList(Class<T> cls,Integer page)  {

		List objList=new ArrayList();
		Connection conn = DbUtils.getConn();
		String tableName = SqlUtils.getTableName(cls);

		PreparedStatement ps = null;
		try {
			Object oTest = Class.forName(cls.getName()).newInstance();// 对象的实例化
			ColInfo cinfo = SqlUtils.getColArr(oTest.getClass());
//			String sql="select  * from (select rownum r2, e.* from (select * from t_user order by id desc) e where rownum <=? ) where r2 >=?" ;
			String colstr = SqlUtils.list2colstr(cinfo.cols2);
			String sql = "select " + colstr + " from (select rownum r2, "+colstr+" from (select "+colstr+" from " + tableName+" order by id desc)  where rownum <=? ) where r2 >=?" ;
			System.out.println(sql);


			ps = conn.prepareStatement(sql);
			Long page1=(long) (page*10-9);
			Long page2=(long) (page*10);
			ps.setLong(1, page2);
			ps.setLong(2, page1);
			ResultSet rs = ps.executeQuery();
	//		ConnectionFactory.commit();
			while (rs.next()) {
				Object o = Class.forName(cls.getName()).newInstance();// 对象的实例化
				// 对传进去的对象o进行赋值
				SqlUtils.setParaByRs(o, cinfo, rs);
				//obj
				objList.add(o);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			//throw e;
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return objList;
	}
	
}
